package com.matt.repository.course;

import com.matt.domain.Children;
import com.matt.domain.course.Course;
import com.matt.domain.course.CourseChild;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2018/11/22
 * *******************************
 */
public interface CourseChildRepository extends JpaRepository<CourseChild, String>{

    /**
     * 通过课程id查询章节列表
     * @param courseId
     * @return
     */
    @Query(value = "select * from course_child where courseId = :courseId and `type`=1 order by priority asc,created desc", nativeQuery = true)
    List<CourseChild> findChildById(@Param("courseId") String courseId);

    /**
     * 通过课程id查询章节列表
     * @param courseId
     * @return
     */
    @Query(value = "select * from course_child where courseId = :courseId and status=0 order by priority asc,created desc", nativeQuery = true)
    List<CourseChild> findAllChildById(@Param("courseId") String courseId);

    /**
     * 通过课程id查询章节数量
     * @param courseId
     * @return
     */
    @Query(value = "select count(1) from course_child where courseId = :courseId and type=1 and status=0", nativeQuery = true)
    Integer findChildCountById(@Param("courseId") String courseId);

    /**
     * 查询子章节数量
     * @param id
     * @return
     */
    @Query(value = "select count(1) from course_child where parentId = :id and status=0", nativeQuery = true)
    Integer findCountById(@Param("id") String id);

    /**
     * 查询子章节数量
     * @param id
     * @return
     */
    @Query(value = "select * from course_child where parentId = :id and status=0 order by priority asc,created desc", nativeQuery = true)
    List<CourseChild> findChildByParentId(@Param("id") String id);


    /**
     * 通过课程id查询章节列表
     * @param ancestorsId
     * @return
     */
    @Query(value = "select * from course_child where ancestorsId = :ancestorsId and type=2 order by priority asc,created desc", nativeQuery = true)
    List<CourseChild> findChildByAncestorsId(@Param("ancestorsId") String ancestorsId);

    /**
     * 通过祖级id删除所有章节
     * @param ancestorsId
     * @return
     */
    @Modifying
    @Query(value = "delete from course_child where ancestorsId =:ancestorsId", nativeQuery = true)
    int deleteByAncestorsId(@Param("ancestorsId") String ancestorsId);

    /**
     * 通过祖级id删除所有子章节
     * @param ancestorsId
     * @return
     */
    @Modifying
    @Query(value = "delete from course_child where ancestorsId =:ancestorsId and type=2", nativeQuery = true)
    int deleteChildByAncestorsId(@Param("ancestorsId") String ancestorsId);


    /**
     * 通过祖级id查询所有章节数量
     * @param ancestorsId
     * @return
     */
    @Query(value = "select count(1) from course_child where ancestorsId = :ancestorsId and status=0 and `type`=2 ", nativeQuery = true)
    Integer findCountByAncestorsId(@Param("ancestorsId") String ancestorsId);

    /**
     * 通过父级id删除子章节信息
     * @param parentId
     * @return
     */
    @Modifying
    @Query(value = "delete from course_child where parentId =:parentId", nativeQuery = true)
    int deleteChildByParentId(@Param("parentId") String parentId);

}
